<?php

$this->PhpExcel->Excel();
$ext = ($t_exel == 1) ? 'xlsx' : 'ods';
$celdas=array('B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC');
$listadoReporte = array('normal' => 'Normal','detallado' => 'Detallado');
$arrStyleTitle = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
                                        'startcolor' => array('rgb' => '81DAF5')),
                        'font' => array('bold' => true, 'color' => array('rgb' => PHPExcel_Style_Color::COLOR_WHITE)),
                        'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));

$arrStyleTitleuno = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'EFFBFB')));
$arrborderTitleuno = array('style' => PHPExcel_Style_Border::BORDER_DASHDOT,'color' => array('rgb' => '000000'));
$valoresAnteriores = array();

$nombre = str_replace(' ', '_', strtoupper(trim($titulo)))."_".date("d_m_Y");
if($tipo==1) {
    $this->PhpExcel->titulo_reporte("SIGEPS UTILITARIO",$this->Interfaz->mayus($titulo));
    $titulo=str_replace(' ','_',$titulo);
    $titulo=substr($titulo,0,18);
    $this->PhpExcel->titulo_hoja($titulo);

    $this->PhpExcel->hoja->getDefaultStyle()->applyFromArray(array('font' => array('name' => 'arial','size' => 10)));
    $this->PhpExcel->hoja->getStyle('A1')->applyFromArray(array('font' => array('bold' => true, 'size' => 12, 'color' => array('rgb' => '8B0000'))));
    $this->PhpExcel->hoja->getStyle('A2')->applyFromArray(array('font' => array('bold' => true, 'size' => 11)));
    $this->PhpExcel->hoja->getStyle('A3')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
    $this->PhpExcel->hoja->getStyle('A4')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));

    $max=count($data[0]);

    $this->PhpExcel->configura_celda('A',5); //#
    
    for($i=0;$i<$max;$i++){
        $this->PhpExcel->configura_celda($celdas[$i],30); //AGENCIA
    }
    
    $this->PhpExcel->hoja->setAutoFilter('A6:'.$celdas[($max-1)].'6'); // AUTO FILTER
    $con=0;
    $tit[] = array('#','STR','C');
    
    foreach ($titulos as $key => $value){
        $tit[] = array($value,'STR','C');
        $con++;
        if($con>$max)
            break;
    }
    
    if($max > (count($tit)-1)){
        $tam=$max - (count($tit)-1);
        for($j=0; $j < $tam; $j++) {
            $tit[] = array('------','STR','C');
        }
    }
    
    $j=0;
    $index_title=6;
    $index_detail=0;

    foreach($tit as $columnTitle) {
        $this->PhpExcel->valor_celda($j, $index_title, $columnTitle);
        $oColumn = $this->PhpExcel->obtener_columna($j, $index_title);
        $oRow = $this->PhpExcel->obtener_fila($j, $index_title);
        $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->applyFromArray($arrStyleTitle);
        $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getBorders()->getAllBorders()->applyFromArray( $arrborderTitleuno);
        if($j > 0) {
            $posicion = array_search($columnTitle[0], $agrupar);
            if($posicion !== false) {
                $agrupar[$oColumn] = $agrupar[$posicion];
                unset($agrupar[$posicion]);
            }
        }
        $j++;
    }
    
    $fila = 7;
    $num=1;
    foreach ($data as $key => $value) {
        $valores = array();
        $valores[] = array($num,'STR','C');
        
        foreach ($value as $llave => $valor) {
            $valores[] = array($this->Interfaz->limpiar_campo($valor),'STR','L');
        }
        
        $columna=0;
        
        foreach($valores as $indice => $valor) {
            $this->PhpExcel->valor_celda($columna, $fila,  $valor);
            $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
            $oRow = $this->PhpExcel->obtener_fila($columna, $fila);
            
            if(!empty($valoresAnteriores) && ($valoresAnteriores[$indice][0] == $valor[0]) && array_key_exists($oColumn, $agrupar)) {
                $filaAnt = ($oRow - 1);
                $this->PhpExcel->hoja->mergeCells("{$oColumn}{$filaAnt}:{$oColumn}{$oRow}");
            }

            if($fila%2==0) {
                $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->applyFromArray($arrStyleTitleuno);
            }

            $this->PhpExcel->hoja->getStyle($oColumn.$oRow)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                    $columna++;
        }
        
        $valoresAnteriores = $valores;
        $fila++;
        $valores=null;
        $num++;
    }
    $this->PhpExcel->mostrar($nombre,$ext);
} else {
    $max=count($data[$tienda_buscar[0]][0]);
    
    if($max==0){
        if(isset($data[$tienda_buscar[0]][1]))
            $max=count($data[$tienda_buscar[0]][1]);
        else
            $max=count($titulos);
    }
    
    $con=1;
    $tit[] = '#';
    
    foreach ($titulos as $key => $value){
        $tit[] = $value;
        $con++;
        if($con>$max)
            break;
    }
    
    if($max > (count($tit)-1)){
        $tam=$max - (count($tit)-1);
        for($j=0; $j < $tam; $j++){
            $tit[] = '------';
        }
    }
    
    for($k=0;$k< count($tienda_buscar);$k++) {
        $valoresAnteriores = array();
        if($k>0) {
            $this->PhpExcel->xls->createSheet();
        }
        
        $this->PhpExcel->xls->setActiveSheetIndex($k);
        $oac=$this->PhpExcel->xls->getActiveSheet();
        $oac->setTitle($tienda_buscar[$k]);
        $oac->getDefaultStyle()->applyFromArray(array('font' => array('name' => 'arial','size' => 10)));
        $oac->getStyle('A1')->applyFromArray(array('font' => array('bold' => true, 'size' => 12, 'color' => array('rgb' => '8B0000'))));
        $oac->getStyle('A2')->applyFromArray(array('font' => array('bold' => true, 'size' => 11)));
        $oac->getStyle('A3')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
        $oac->getStyle('A4')->applyFromArray(array('font' => array('bold' => false,'size' => 10)));
        $oac->setCellValue('A1', "AGROPATRIA - SIGESP UTILITARIO");
        $oac->setCellValue('A2', $this->Interfaz->mayus($titulo).' TIENDA '.$this->Interfaz->mayus($tienda[$tienda_buscar[$k]]) );
        $oac->setCellValue('A3', "Fecha: ".date("d-m-Y"));
        $oac->setCellValue('A4', "Hora: ".date("h:i"));
        $oac->getColumnDimension('A')->setWidth(5);
        
        for($i=0;$i<$max;$i++){
            $oac->getColumnDimension($celdas[$i])->setWidth(30);
        }
        
        $oac->setAutoFilter('A6:'.$celdas[($max-1)].'6'); // AUTO FILTER
        $index_title=6;
        $index_detail=0;
        $j=0;
        foreach($tit as $columnTitle) {
            @$oac->getCellByColumnAndRow($j,$index_title)->setValueExplicit($columnTitle, PHPExcel_Cell_DataType::TYPE_STRING);
            $oC = $oac->getCellByColumnAndRow($j, $index_title)->getColumn();
            $oR = $oac->getCellByColumnAndRow($j, $index_title)->getRow();
            $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
            $oac->getStyle($oC.$oR)->applyFromArray($arrStyleTitle);
            $oac->getStyle($oC.$oR)->getBorders()->getAllBorders()->applyFromArray( $arrborderTitleuno);
            if(($k == 0) && ($j > 0)) {
                $posicion = array_search($columnTitle, $agrupar);
                if($posicion !== false) {
                    $agrupar[$oC] = $agrupar[$posicion];
                    unset($agrupar[$posicion]);
                }
            }
            $j++;
        }
        $fila = 7;		
        $num=1;
        if(is_array($data[$tienda_buscar[$k]]) && count($data[$tienda_buscar[$k]])>0){
            foreach ($data[$tienda_buscar[$k]] as $key => $value) {
                $valores=array();
                $valores[] = array($num,'STR','C');

                foreach ($value as $llave => $valor){
                    $valores[] = array($this->Interfaz->limpiar_campo($valor),'STR','L');
                }

                $columna=$x=0;
                foreach($valores as $indice => $valor) {
                    if($valor[1]=='STR')
                        $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($valor[0], PHPExcel_Cell_DataType::TYPE_STRING);
                    else
                        $oac->getCellByColumnAndRow($columna,$fila)->setValueExplicit($valor[0], PHPExcel_Cell_DataType::TYPE_NUMERIC);

                    $oC = $oac->getCellByColumnAndRow($columna, $fila)->getColumn();
                    $oR = $oac->getCellByColumnAndRow($columna, $fila)->getRow();

                    if($valor[2]=='L')
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                    else if($valor[2]=='C')
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    else
                        $oac->getStyle($oC.$oR)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    
                    if(!empty($valoresAnteriores) && ($valoresAnteriores[$indice][0] == $valor[0]) && array_key_exists($oC, $agrupar)) {
                        $filaAnt = ($oR - 1);
                        $oac->mergeCells("{$oC}{$filaAnt}:{$oC}{$oR}");
                    }

                    if($fila%2==0) {
                        $oac->getStyle($oC.$oR)->applyFromArray($arrStyleTitleuno);
                    }	        
                    $oac->getStyle($oC.$oR)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                    $columna++;
                }
                $valoresAnteriores = $valores;
                $fila++;
                $num++;
                $valores=null;
            }
        }
        unset($valoresAnteriores);
    }
    $this->PhpExcel->xls->setActiveSheetIndex(0);
    $this->PhpExcel->mostrar($nombre,$ext);
}
?>